import pyodbc
import logging
import configparser
import os
import sys
from cryptography.fernet import Fernet
# 配置日志记录到文件
logging.basicConfig(filename='excption.log', level=logging.ERROR, format='%(asctime)s - %(name)s - %(levelname)s - %(message)s')


key = 'lzbVgGHnKZaGWs9wBnV5HM_VFSoKzKNTyH4J5_zZAeI='

def decrypt(encrypted_value, key):
    f = Fernet(key)
    decrypted_value = f.decrypt(encrypted_value.encode('utf-8'))
    return decrypted_value.decode('utf-8')


# 创建配置文件解析器
config = configparser.ConfigParser()
# \ufeff 是 UTF-8 文件的字节顺序标记 (BOM)
# 使用 encoding='utf-8-sig' 来处理潜在的 BOM
# 读取配置文件
os.chdir(os.path.dirname(os.path.abspath(sys.argv[0])))
config.read('configs.ini', encoding='utf-8-sig')

driver = config['database']['driver']
server = config['database']['server']
database = config['database']['database']
# 解密用户名和密码
encrypted_username = config['database']['username']
encrypted_password = config['database']['password']
username = decrypt(encrypted_username, key)
password = decrypt(encrypted_password, key)
connection_string = f"DRIVER={driver};SERVER={server};DATABASE={database};UID={username};PWD={password}"

class SQLServerDatabase:
    def __init__(self, connection_string):
        self.connection_string = connection_string
        self.conn = None
        self.cursor = None

    def __enter__(self):
        # 在__enter__方法中建立数据库连接和游标
        self.conn = pyodbc.connect(self.connection_string)
        self.cursor = self.conn.cursor()
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        if self.cursor:
            self.cursor.close()
        if self.conn:
            if exc_type:
                self.conn.rollback()
            else:
                self.conn.commit()
            self.conn.close()

    def _fetchall_as_dicts(self, cursor):
        """
        获取查询结果并将其映射为字典

        Args:
            cursor: PyODBC 游标对象

        Returns:
            list: 包含字典的结果集
        """
        columns = [column[0] for column in cursor.description]
        result = []
        for row in cursor.fetchall():
            result.append(dict(zip(columns, row)))
        return result

    def _execute(self, query, params=None, fetch=True, total_rows=0, page = 0):
        try:
            with pyodbc.connect(self.connection_string) as conn:
                cursor = conn.cursor()
                if params:
                    cursor.execute(query, tuple(params))
                else:
                    cursor.execute(query)
                # print(query,params)
                if fetch:
                    columns = [column[0] for column in cursor.description]
                    result = []
                    for row in cursor.fetchall():
                        result.append(dict(zip(columns, row)))
                    data = result
                else:
                    data = []

            return {"data": data, "message": "success", "code": 200, "total_rows": total_rows, "page": page}
        except pyodbc.Error as e:
            logging.error(f"SQL Execution failed: {query}, params: {params}")
            return {"data": [], "message": str(e), "code": 500}

    # 不分页查询
    def execute_query_all(self, table, filters=None, params=None, customSelectColumns=None):
        """
        执行数据库查询操作，获取所有符合条件的数据

        Args:
            table (str): 数据库表名
            filters (str, optional): SQL查询的过滤条件. Defaults to None.
            params (list, optional): SQL查询的参数列表. Defaults to None.

        Returns:
            dict: 包含查询结果的字典，格式为 {"data": 数据, "message": 消息, "code": 状态码}
        """
        query = f"SELECT * FROM {table}"
        if filters:
            query += f" WHERE {filters}"
        # 默认是查询全部列，如果传递了customSelectColumns参数，则根据customSelectColumns内容进行自定义查询
        if customSelectColumns:
            # 查找第一个 * 的索引
            first_asterisk_index = query.find('*')

            # 如果找到了 *，则进行替换
            if first_asterisk_index != -1:
                query = query[:first_asterisk_index] + customSelectColumns + query[first_asterisk_index + 1:]
        # 添加 ORDER BY 子句，按照 id 列倒序排列
        # 如果原语句中没有 ORDER BY 语句则加上，否则不加
        if 'ORDER BY' not in query:
            query += " ORDER BY id ASC"
        return self._execute(query, params)

    # 分页查询
    def execute_query_pagination(self, table, filters=None, page=1, page_size=10, params=None, customSelectColumns = None):
        """
        执行数据库分页查询操作，并返回总条数

        Args:
            table (str): 数据库表名
            filters (str, optional): SQL查询的过滤条件. Defaults to None.
            page (int, optional): 页码. Defaults to 1.
            page_size (int, optional): 每页的数据条数. Defaults to 10.
            params (list, optional): SQL查询的参数列表. Defaults to None.

        Returns:
            dict: 包含查询结果和总条数的字典，格式为 {"data": 数据, "message": 消息, "code": 状态码, "total_rows": 总条数}
        """
        # Execute a COUNT(*) query to get the total number of rows
        count_query = f"SELECT COUNT(*) FROM {table}"
        if filters:
            count_query += f" WHERE {filters}"

        total_rows = 0  # Initialize to 0 by default

        with pyodbc.connect(self.connection_string) as conn:
            cursor = conn.cursor()

            if params:
                cursor.execute(count_query, tuple(params))
            else:
                cursor.execute(count_query)

            count_result = cursor.fetchone()
            if count_result:
                total_rows = count_result[0]

        # Now execute the main query for pagination
        query = f"""SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY id ASC) AS rn FROM {table} {f"WHERE {filters}" if filters else ""}) AS subquery WHERE rn > ? AND rn <= ?"""
        # 如果原语句中没有 ORDER BY 语句则加上，否则不加
        if 'ORDER BY' not in query:
            query += " ORDER BY id ASC"
        # 默认是查询全部列，如果传递了customSelectColumns参数，则根据customSelectColumns内容进行自定义查询
        if customSelectColumns:
            # 查找第一个 * 的索引
            first_asterisk_index = query.find('*')

            # 如果找到了 *，则进行替换
            if first_asterisk_index != -1:
                query = query[:first_asterisk_index] + customSelectColumns + query[first_asterisk_index + 1:]
        if not params:
            params = []
        params.extend([(page - 1) * page_size, page * page_size])
        return self._execute(query, params, page=page, total_rows=total_rows)

    def execute_insert(self, table, values, columns=None):
        """
        执行数据库插入操作

        Args:
            table (str): 数据库表名
            values (list): 插入的数据值，作为列表传递
            columns (list, optional): 插入的列名. Defaults to None.

        Returns:
            dict: 包含插入结果的字典，格式为 {"data": 数据, "message": 消息, "code": 状态码}
        """
        placeholders = ",".join(["?" for _ in range(len(values))])
        if columns:
            columns = ",".join(columns)
            query = f"INSERT INTO {table} ({columns}) VALUES ({placeholders})"
        else:
            query = f"INSERT INTO {table} VALUES ({placeholders})"
        return self._execute(query, values, fetch=False)

    def execute_update(self, table, params, filters=None):
        """
        执行数据库更新操作

        Args:
            table (str): 数据库表名
            params (dict): 要更新的数据，以字典形式传入
            filters (str, optional): SQL查询的过滤条件. Defaults to None.

        Returns:
            dict: 包含更新结果的字典，格式为 {"data": 数据, "message": 消息, "code": 状态码}
        """
        set_values = ", ".join([f"{key} = ?" for key in params.keys()])
        query = f"UPDATE {table} SET {set_values}"
        if filters:
            query += f" WHERE {filters}"
        return self._execute(query, list(params.values()), fetch=False)

    def execute_delete(self, table, filters, params=None):
        """
        执行数据库删除操作

        Args:
            table (str): 数据库表名
            filters (str): SQL查询的过滤条件
            params (list, optional): SQL查询的参数列表. Defaults to None.

        Returns:
            dict: 包含删除结果的字典，格式为 {"data": 数据, "message": 消息, "code": 状态码}
        """
        query = f"DELETE FROM {table} WHERE {filters}"
        return self._execute(query, params, fetch=False)


# 使用示例
if __name__ == "__main__":

    # 建立数据库连接字符串
    # connection_string = "DRIVER={SQL Server};SERVER=server_name;DATABASE=database_name;UID=user_id;PWD=password"
    print(connection_string)
    # 创建 SQLServerDatabase 实例
    with SQLServerDatabase(connection_string) as db:

        # 执行查询操作示例
        # query_result = db.execute_query("test", filters="id = ?", page=1, page_size=10, params=['1'])


        # # 执行插入操作示例
        # insert_result = db.execute_insert("test", values=["ww1"], columns=["name"])

        # insert_result = db.execute_insert("test", values=["ww1","34","1","12031023"], columns=["name","age","gender","tel"])
        #
        # print(insert_result)
        #
        # # 执行更新操作示例
        # update_result = db.execute_update("test", params={"age": "1","tel":"13085295912"}, filters=f"id = ${1}")
        # print(update_result)
        #
        # 执行删除操作示例
        # delete_result = db.execute_delete("test", filters="id = ?", params=[35])
        # print(delete_result)

        # query_result = db.execute_query("test", page=1, page_size=10)
        query_result = db.execute_query_pagination("test", filters="id = ? and age = ?", page=1, page_size=10, params=['2','23'])
        print(query_result)